﻿CREATE TRIGGER tr_Borrower_ProcessSSN
ON v_Borrower_ProcessSSN
INSTEAD OF INSERT
AS

DECLARE @IsEdit bit SET @IsEdit = 0
UPDATE B SET SSN = dbo.CleanEditValue(B.SSN)
FROM t_Borrower B INNER JOIN inserted i ON B.ID = i.BorrowerID
WHERE dbo.IsEdit(B.SSN) = 1

IF @@ROWCOUNT > 0 SET @IsEdit = 1

UPDATE t_Borrower SET SSN = LTRIM(RTRIM(dbo.clr_RegEx_Replace(SSN,'[-\[\]]','',-1,0,1)))
FROM t_Borrower B INNER JOIN inserted i ON B.ID = i.BorrowerID

DECLARE @SSN sysname,@LoanID int
SELECT @SSN = SSN
FROM t_Borrower B INNER JOIN inserted i ON B.ID = i.BorrowerID
WHERE isnull([SSN],'') > '' AND
			NOT [SSN] like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'

IF NOT @SSN IS NULL BEGIN
	ROLLBACK TRAN
	RAISERROR('SSN [%s] must have 9 digits.',16,1,@SSN)
	RETURN
END


IF NOT EXISTS(SELECT * FROM t_Borrower B INNER JOIN inserted i ON B.ID = i.BorrowerID AND B.SSN > '' )RETURN

DECLARE @ErrMsg sysname
SELECT @ErrMsg = dbo.sprint2('LoanID:%s SSN:%s',L_B.LoanID,B.SSN) FROM inserted i
					INNER JOIN t_Loan_Borrower L_B ON L_B.BorrowerID = i.BorrowerID
					INNER JOIN t_Loan_Borrower L_B_1 ON L_B_1.LoanID = L_B.LoanID
					INNER JOIN t_Borrower B ON B.ID = L_B_1.BorrowerID
					GROUP BY L_B.LoanID,B.SSN HAVING COUNT(*)>1
IF @ErrMsg > '' BEGIN
	ROLLBACK TRAN
	RAISERROR('Duplicated co-borrower''s SSN
%s',16,1,@ErrMsg)
	RETURN
END	

IF @IsEdit = 1 RETURN

DECLARE @BID int
SELECT @BID = B1.ID
FROM t_Borrower B
INNER JOIN inserted i ON B.ID = i.BorrowerID
INNER JOIN t_Borrower B1 ON B.SSN = B1.SSN AND B.ID <> B1.ID

IF NOT @BID IS NULL BEGIN
	IF (SELECT COUNT(*) FROM t_Loan_Borrower L_B INNER JOIN inserted i ON i.BorrowerID = L_B.BorrowerID) > 1 BEGIN
		ROLLBACK TRAN
		DECLARE @Text nvarchar(max)
		SELECT @Text = ISNULL(@Text+char(10),'')+dbo.sprint1('LoanID:%s',LoanID)
		FROM t_Loan_Borrower L_B INNER JOIN inserted i ON L_B.BorrowerID = i.BorrowerID
		RAISERROR('
You have entered SSN that belongs to another borrower,
but current borrower belongs to more then one loan and
system is unable to automaticaly update it.
%s

If you need to correct wrong SSN then use [SSN] notation.
If you need to enter a new borrower - remove this one from the loan
and create a new one from the scratch.
',16,1,@Text)
		RETURN
	END
	UPDATE L_B SET BorrowerID = @BID FROM t_Loan_Borrower L_B INNER JOIN inserted i ON i.BorrowerID = L_B.BorrowerID
	UPDATE B SET SSN = SSN_Old FROM t_Borrower B INNER JOIN inserted i ON B.ID = i.BorrowerID
	INSERT INTO v_Borrower_DeleteIfEmpty SELECT BorrowerID FROM inserted
--	DECLARE @SSN sysname
--	SELECT @SSN = SSN FROM inserted
--	RAISERROR('Borrower %s already has this SSN:%s.',16,1,@Borrower,@SSN)
--	ROLLBACK TRAN
	RETURN
END


